import * as XLSX from 'xlsx';
import fs from 'node:fs';
import path from 'node:path';
import { TARGET_SHEETS, SHEXIAO_HEADER, YUFU_HEADER } from './constants';
import { CREDIT_SALES_SHEETS, PREPAYMENT_SHEETS } from './constants';
import { FileStatus } from '@/types';

// 初始化 XLSX 的文件系统
XLSX.set_fs(fs);

export async function analyzeExcel(filePath: string) {
  try {
    const workbook = XLSX.readFile(filePath);

    // 判断目标工作表是否存在，如存在则记录相应标记
    const sheetChecks = TARGET_SHEETS.map(sheet => workbook.SheetNames.includes(sheet));
    const sheetStatus = {
      hasShexiaoSuliao: sheetChecks[0],
      hasShexiaoGuangdong: sheetChecks[1],
      hasShexiaoHainan: sheetChecks[2],
      hasYufuSuliao: sheetChecks[3],
      hasYufuGuangdong: sheetChecks[4],
      hasYufuHainan: sheetChecks[5]
    };
    // 检查所有表格的表头
    for (let i = 0; i < TARGET_SHEETS.length; i++) {
      if (sheetChecks[i]) {
        const sheet = workbook.Sheets[TARGET_SHEETS[i]];
        const isYufu = i >= 3; // 索引3及以后的是预付表格
        const headerTemplate = isYufu ? YUFU_HEADER : SHEXIAO_HEADER;

        const headerContent = headerTemplate.map((_, index) => {
          const cellAddress = XLSX.utils.encode_cell({ r: 3, c: index });
          const cell = sheet[cellAddress];
          return cell?.v || '';
        }).join('^');

        if (headerContent !== headerTemplate.join('^')) {
          // 找出表头差异
          const actualHeaders = headerContent.split('^');
          const differences = headerTemplate.map((expected, index) => {
            const actual = actualHeaders[index] || '';
            if (expected !== actual) {
              return `第${index + 1}列: 期望"${expected}", 实际"${actual}"`;
            }
            return null;
          }).filter(diff => diff !== null);

          return {
            success: false,
            error: `❌(${TARGET_SHEETS[i]}) 表头不正确，差异：${differences.join('; ')}`,
            ...sheetStatus
          };
        }
      }
    }

    return {
      ...sheetStatus,
      success: true,

    };

  } catch (error) {
    return {
      success: false,
      error: (error as Error).message
    };
  }
}

export async function mergeExcel(filePath: string, files: FileStatus[], webContents: Electron.WebContents) {
  // 修改参数类型为 WebContents

  // 定义起始行
  const headerRow = 4;
  let mergedShexiaoData = [];
  let mergedYufuData = [];
  const dataStartRowIndex = headerRow;
  const newSheXiaoHeaders = ["文件", "平台", ...SHEXIAO_HEADER];
  const newYufuHeaders = ["文件", "平台", ...YUFU_HEADER];

  try {
    // 读取所有文件
    for (const file of files) {
      const fullFilePath = path.join(filePath, file.fileName);
      const fileNameWithoutExt = file.fileName.split('.').slice(0, -1).join('.');
      const workbook = XLSX.readFile(fullFilePath);

      // 合并赊销数据
      for (const [sheetName, platform] of Object.entries(CREDIT_SALES_SHEETS)) {
        if (workbook.SheetNames.includes(sheetName)) {
          // 使用一致的事件名称
          webContents.send('excel:merge-progress', `处理${file.fileName}的${sheetName}表格`);
          const sheet = workbook.Sheets[sheetName];
          const data = XLSX.utils.sheet_to_json<any[]>(sheet, { header: 1, range: dataStartRowIndex, defval: '' });
          const filteredData = data
            .filter(row =>
              row[0] !== undefined &&
              row[0] !== '' &&
              row[0] !== '汇总' &&
              row[0] !== '总计'
            )
            .map(row => [fileNameWithoutExt, platform, ...row.slice(0, SHEXIAO_HEADER.length)]);

          mergedShexiaoData.push(...filteredData);
        }
      }
      // 合并预付数据
      for (const [sheetName, platform] of Object.entries(PREPAYMENT_SHEETS)) {
        if (workbook.SheetNames.includes(sheetName)) {
          webContents.send('excel:merge-progress', `处理${file.fileName}的${sheetName}表格`);
          const sheet = workbook.Sheets[sheetName];
          const data = XLSX.utils.sheet_to_json<any[]>(sheet, { header: 1, range: dataStartRowIndex, defval: '' });
          const filteredData = data
            .filter(row =>
              row[0] !== undefined &&
              row[0] !== '' &&
              row[0] !== '汇总' &&
              row[0] !== '总计'
            )
            .map(row => [file.fileName, platform, ...row.slice(0, YUFU_HEADER.length)]);

          mergedYufuData.push(...filteredData);
        }
      }


    }
    // 创建新工作表
    const shexiaoSheet = XLSX.utils.aoa_to_sheet([newSheXiaoHeaders, ...mergedShexiaoData]);
    const yufuSheet = XLSX.utils.aoa_to_sheet([newYufuHeaders, ...mergedYufuData]);
    // 获取第9列和第10列的列标识
    const col9 = XLSX.utils.encode_col(8); // 0-based，所以第9列是8
    const col10 = XLSX.utils.encode_col(9);
    const col20 = XLSX.utils.encode_col(19);
    const col22 = XLSX.utils.encode_col(21);

    const col19 = XLSX.utils.encode_col(18);
    const col21 = XLSX.utils.encode_col(20);

    // 设置日期格式
    const dateFormat = { numFmt: 'yyyy/m/d' };
    for (let i = 1; i <= mergedShexiaoData.length; i++) { // 从1开始跳过表头
      shexiaoSheet[`${col9}${i + 1}`].z = dateFormat.numFmt;
      shexiaoSheet[`${col10}${i + 1}`].z = dateFormat.numFmt;
      shexiaoSheet[`${col20}${i + 1}`].z = dateFormat.numFmt;
      shexiaoSheet[`${col22}${i + 1}`].z = dateFormat.numFmt;
    }

    for (let i = 1; i <= mergedYufuData.length; i++) { // 从1开始跳过表头
      yufuSheet[`${col9}${i + 1}`].z = dateFormat.numFmt;
      yufuSheet[`${col10}${i + 1}`].z = dateFormat.numFmt;
      yufuSheet[`${col19}${i + 1}`].z = dateFormat.numFmt;
      yufuSheet[`${col21}${i + 1}`].z = dateFormat.numFmt;
    }



    // 生成时间戳 (UTC+8)
    const now = new Date();
    const timestamp = new Intl.DateTimeFormat('zh-CN', {
      timeZone: 'Asia/Shanghai',
      year: 'numeric',
      month: '2-digit',
      day: '2-digit',
      hour: '2-digit',
      minute: '2-digit',
      second: '2-digit',
      hour12: false
    }).format(now).replace(/[/:]/g, '').replace(/\s/, '_');
    const outputDir = path.join(filePath, '合并结果');
    if (!fs.existsSync(outputDir)) {
      fs.mkdirSync(outputDir);
    }
    // 创建工作簿并保存
    const newWorkbook = XLSX.utils.book_new();
    XLSX.utils.book_append_sheet(newWorkbook, shexiaoSheet, '赊销数据合并');
    XLSX.utils.book_append_sheet(newWorkbook, yufuSheet, '预付数据合并');
    
    // 添加压缩选项
    const options = {
      compression: true,
      bookType: 'xlsx' as const
    };
    
    XLSX.writeFile(newWorkbook, path.join(outputDir, `合并报表_${timestamp}.xlsx`), options);

    return {
      success: true,
      message: `合并成功 - 文件名：合并报表_${timestamp}.xlsx`

    }
  } catch (error) {
    return {
      success: false,
      error: (error as Error).message
    };
  }
}